﻿Imports Microsoft.VisualBasic
Imports System.Data

<System.ComponentModel.DataObjectAttribute(True)> _
Public Class BLL_QueryTrainingRegister

    Public Sub New()
        ' 
        ' TODO: 在此加入建構函式的程式碼 
        ' 
    End Sub

    'SELECT 
    <System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.[Select])> _
    Public Function BLL_Select(ByVal LoginUser As String) As DataTable

        'Dim sql As New MyOrganizationDataFilter
        Dim sql As New MyStringBuilder
        Dim dat As New clsData

        'sql.AppendFormat("SELECT A.GroupId,B.GroupName,D.* ")
        'sql.AppendFormat("FROM (SELECT distinct GroupId FROM TrainingRegister WHERE CreateBy='{0}') A  ", LoginUser)
        'sql.AppendFormat("inner join TrainingCourseGroup B on A.GroupId=B.id ")
        'sql.AppendFormat("left join TrainingCourseGroupMapping C on A.GroupId= C.GroupId ")
        'sql.AppendFormat("left join TrainingCourse D on C.CourseId = D.Id ")

        sql.AppendFormat("SELECT tcg.* ")
        sql.AppendFormat(", isnull(tr.RegisterCount, 0) RegisterCount ")
        sql.AppendFormat(", o.OrganizationName ")
        sql.AppendFormat("FROM TrainingCourseGroup tcg ")
        sql.AppendFormat("left join (select GroupID, Count(*) RegisterCount from TrainingRegister group by GroupID) tr on tcg.ID = tr.GroupID ")
        sql.AppendFormat("inner join OrganizationView o on tcg.UOrganizationID = o.UOrganizationID ")
        sql.AppendFormat("WHERE getDate() BETWEEN RegStartDate AND dateadd(d,1,RegEndDate) ")
        sql.AppendFormat("order by StartDate  ")
        Return dat.GetData(sql)

    End Function

End Class
